Sub Report()

' Prototype Written by chaoyuew@cn.ibm.com
' Updated on 13/07/2015  Basic Structure
' Updated on  14/07/2015  Set down the copy part code.
' Updated on  19/07/2015 Set down adjust part code
' Updated on  23/07/2015 Save Macro to be permanent

Dim file(1 To 6), to_day!, num!, temp!, last_row%, active_unit%, unit As Range
'Public last_row%
'<---------------------------Part1 Get the file name
' ***Automatically generate the file name based on that all the files have fixed naming rule
' ***This will save much time, however will get error if naming rule changes

to_day = Day(Date) - 9
file(1) = "Pending_Incident_Intel_" & to_day & "Jul2015_0800AM.xls"
file(2) = "Pending_Incident_Unix_" & to_day & "Jul2015_0800AM.xls"
file(3) = "Pending_Ticket_Intel_" & to_day & "Jul2015_0800AM.xls"
file(4) = "Pending_Ticket_NZ_Intel_SR_" & to_day & "Jul2015_0800AM.xls"
file(5) = "Pending_Ticket_NZ_Unix_SR_" & to_day & "Jul2015_0800AM.xls"
'file(6) = "Pending_Ticket_Unix_" & to_day & "Jul2015_0800AM.xls"

Application.DisplayAlerts = False
'<--------------------------- Part2 Copy all the file data into one excel
For num = 1 To 5
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & file(num)
'    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    last_row = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    temp = last_row - 5
    If ActiveSheet.Cells(temp, 1).Value Like "SR*" Or ActiveSheet.Cells(temp, 1).Value Like "IN*" Then
        Application.Union(ActiveSheet.Range("A5:G" & temp), ActiveSheet.Range("J5:N" & temp), ActiveSheet.Range("P5:P" & temp), ActiveSheet.Range("R5:R" & temp), ActiveSheet.Range("T5:T" & temp)).Select
'        Application.Union(Range("A5:G" & temp), Range("J5:N" & temp), Range("P5:P" & temp), Range("R5:R" & temp), Range("T5:T" & temp)).Copy
        Selection.Copy
        ThisWorkbook.Activate
        last_row = Cells(Rows.Count, 1).End(xlUp).Row
        temp = last_row + 1
        Cells(temp, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End If
Workbooks(file(num)).Close
Next
'<--------------------------- Part3 Adjust the data to be easily reviewed
last_row = Cells(Rows.Count, 1).End(xlUp).Row
For Each unit In Range("E2:E" & last_row)
    If unit.Value <> "" Then unit.Offset(, -1).Value = unit.Value
Next
For Each unit In Range("K2:K" & last_row)
    If unit.Value <> "" Then unit.Offset(, -2).Value = unit.Value
Next
Range("E:E,J:K").Delete
'<---------------------------Part4 Insert Performer Column
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "Performer"
Application.DisplayAlerts = True
End Sub

Sub ChangeColour()
last_row = Cells(Rows.Count, 1).End(xlUp).Row
For Each unit In Range("N2:N" & last_row)
    Union(unit.Offset(0, -2), unit.Offset(0, -6), unit.Offset(0, -13)).Select
    If unit.Value > 9 Then
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    ElseIf unit.Value > 3 Then
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
Next
End Sub

'cells.clear '清除单元格全部，包含内容、格式、批注
' .ClearComments '清除单元格批注
' .ClearContents '清除单元格内容
' .ClearFormats '清除单元格格式

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Selection.Row > 7 Then
'Range("a8:a129").EntireRow.ClearFormats
'        With Selection.Interior
'            .Pattern = xlSolid
'            .PatternColorIndex = xlAutomatic
'            .Color = 65535
'            .TintAndShade = 0
'            .PatternTintAndShade = 0
'        End With
'End If
'End Sub

